<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0"><title>Mysql数据库常用命令 | 学途路漫漫</title><meta name="author" content="学途路漫漫"><meta name="copyright" content="学途路漫漫"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="登陆数据库：mysql -h localhost -u root -p 1.数据库操作显示数据库：show databases; 创建数据库：create database if not exists [数据库名字] 创建数据库并指定字符集：create database [库名] character set gbk; 删除数据库：drop database [库名]; 显示警告信息：show w">
<meta property="og:type" content="article">
<meta property="og:title" content="Mysql数据库常用命令">
<meta property="og:url" content="http://example.com/2020/04/03/Mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/index.html">
<meta property="og:site_name" content="学途路漫漫">
<meta property="og:description" content="登陆数据库：mysql -h localhost -u root -p 1.数据库操作显示数据库：show databases; 创建数据库：create database if not exists [数据库名字] 创建数据库并指定字符集：create database [库名] character set gbk; 删除数据库：drop database [库名]; 显示警告信息：show w">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://s1.ax1x.com/2023/04/02/ppfOMMd.jpg">
<meta property="article:published_time" content="2020-04-03T13:22:44.000Z">
<meta property="article:modified_time" content="2023-04-03T10:20:03.347Z">
<meta property="article:author" content="学途路漫漫">
<meta property="article:tag" content="MySql">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://s1.ax1x.com/2023/04/02/ppfOMMd.jpg"><link rel="shortcut icon" href="/img/avatar.gif"><link rel="canonical" href="http://example.com/2020/04/03/Mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/index.html"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox.min.css" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = { 
  root: '/',
  algolia: undefined,
  localSearch: undefined,
  translate: {"defaultEncoding":2,"translateDelay":0,"msgToTraditionalChinese":"繁","msgToSimplifiedChinese":"簡"},
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '',
  date_suffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: undefined,
  lightbox: 'fancybox',
  Snackbar: undefined,
  source: {
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery/dist/fjGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery/dist/fjGallery.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isAnchor: false,
  percent: {
    toc: true,
    rightside: false,
  }
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
  title: 'Mysql数据库常用命令',
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2023-04-03 18:20:03'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
    win.getCSS = (url,id = false) => new Promise((resolve, reject) => {
      const link = document.createElement('link')
      link.rel = 'stylesheet'
      link.href = url
      if (id) link.id = id
      link.onerror = reject
      link.onload = link.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        link.onload = link.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(link)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
    const detectApple = () => {
      if(/iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)){
        document.documentElement.classList.add('apple')
      }
    }
    detectApple()
    })(window)</script><meta name="generator" content="Hexo 6.3.0"></head><body><div id="loading-box"><div class="loading-left-bg"></div><div class="loading-right-bg"></div><div class="spinner-box"><div class="configure-border-1"><div class="configure-core"></div></div><div class="configure-border-2"><div class="configure-core"></div></div><div class="loading-word">加载中...</div></div></div><script>const preloader = {
  endLoading: () => {
    document.body.style.overflow = 'auto';
    document.getElementById('loading-box').classList.add("loaded")
  },
  initLoading: () => {
    document.body.style.overflow = '';
    document.getElementById('loading-box').classList.remove("loaded")

  }
}
window.addEventListener('load',()=> { preloader.endLoading() })

if (false) {
  document.addEventListener('pjax:send', () => { preloader.initLoading() })
  document.addEventListener('pjax:complete', () => { preloader.endLoading() })
}</script><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img is-center"><img src="/img/avatar.gif" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="sidebar-site-data site-data is-center"><a href="/archives/"><div class="headline">文章</div><div class="length-num">7</div></a><a href="/tags/"><div class="headline">标签</div><div class="length-num">8</div></a><a href="/categories/"><div class="headline">分类</div><div class="length-num">10</div></a></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 博客</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header" style="background-image: url('https://s1.ax1x.com/2023/04/02/ppfOMMd.jpg')"><nav id="nav"><span id="blog-info"><a href="/" title="学途路漫漫"></a></span><div id="menus"><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 博客</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div></div><div id="toggle-menu"><a class="site-page" href="javascript:void(0);"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">Mysql数据库常用命令</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2020-04-03T13:22:44.000Z" title="发表于 2020-04-03 21:22:44">2020-04-03</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2023-04-03T10:20:03.347Z" title="更新于 2023-04-03 18:20:03">2023-04-03</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="Mysql数据库常用命令"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">阅读量:</span><span id="busuanzi_value_page_pv"><i class="fa-solid fa-spinner fa-spin"></i></span></span></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><p>登陆数据库：mysql -h localhost -u root -p</p>
<h1 id="1-数据库操作"><a href="#1-数据库操作" class="headerlink" title="1.数据库操作"></a>1.数据库操作</h1><p>显示数据库：<code>show databases;</code></p>
<p>创建数据库：<code>create database if not exists [数据库名字]</code></p>
<p>创建数据库并指定字符集：<code>create database [库名] character set gbk;</code></p>
<p>删除数据库：<code>drop database [库名];</code></p>
<p>显示警告信息：<code>show warnings;</code></p>
<h1 id="2-数据表操作"><a href="#2-数据表操作" class="headerlink" title="2.数据表操作"></a>2.数据表操作</h1><p>创建数据表:</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE 表名</span><br><span class="line">(</span><br><span class="line">	字段名1 数据类型[完整的约束条件],</span><br><span class="line">	字段名2 数据类型[完整的约束条件]</span><br><span class="line">)ENGINE=InnoDB DEFAULT CHARSET=utf8;	</span><br></pre></td></tr></table></figure>

<p>数据表查询</p>
<ul>
<li><p>查看数据表：</p>
<p>​	<code>SHOW TABLES;</code></p>
</li>
<li><p>查看数据表结构：</p>
<p>​	<code>DESCRIBE 表名;</code></p>
</li>
<li><p>查看表的创建信息：</p>
<p>​	<code>show create table 表名[\G];</code></p>
</li>
</ul>
<p>对表操作</p>
<ul>
<li><h4 id="修改表名"><a href="#修改表名" class="headerlink" title="修改表名"></a>修改表名</h4><p>ALTER TABLE 旧表名 RENAME [TO] 新表名;<br>  例子：<code>alter table sc rename score;</code></p>
</li>
<li><h4 id="修改表的存储引擎"><a href="#修改表的存储引擎" class="headerlink" title="修改表的存储引擎"></a>修改表的存储引擎</h4><p>ALTER TABLE 表名 ENGINE&#x3D;&lt;更改后的存储引擎名&gt;;<br>  例子：<code>alter table student engine = myisam;</code></p>
</li>
<li><h4 id="修改表的字符集"><a href="#修改表的字符集" class="headerlink" title="修改表的字符集"></a>修改表的字符集</h4><p>ALTER TABLE 表名 CHARACTER SET 更改后的字符集 ;<br>例子：<code>alter table student modify sno varchar(10) character set utf8;</code></p>
</li>
<li><h4 id="复制表"><a href="#复制表" class="headerlink" title="复制表"></a>复制表</h4><p><code>create table 新表名 select * from 旧表;</code></p>
</li>
<li><h4 id="删除表"><a href="#删除表" class="headerlink" title="删除表"></a>删除表</h4><p><code>drop table 表名;</code></p>
</li>
</ul>
<h1 id="3-字段操作"><a href="#3-字段操作" class="headerlink" title="3.字段操作"></a>3.字段操作</h1><p>​	</p>
<ul>
<li><p>字段约束目录</p>
<ul>
<li>PRIMARY KEY 主键约束			<br>NOT NULL 非空约束			<br> UNIQUE 唯一约束			<br> DEFAULT 默认值约束			<br> FOREIGN KEY 外键约束			<br> AUTO_INCREMENT 自动增加值</li>
</ul>
<p>			</p>
</li>
<li><h4 id="增删改字段操作"><a href="#增删改字段操作" class="headerlink" title="增删改字段操作"></a>增删改字段操作</h4><ul>
<li>添加字段<br>ALTER TABLE 表名 ADD  新字段名  数据类型 [列级约束条件]<br>例子：<code>alter table student add class char(10) after ssex;</code></li>
<li>删除字段<br>ALTER TABLE 表名 DROP 字段名;<br> 例子：<code>alter table student drop class;</code></li>
<li>修改字段的数据类型<br>ALTER TABLE 表名 MODIFY 字段名  数据类型;<br> 例子：<code>alter table product modify ProductID int(11) AUTO_INCREMENT;</code></li>
<li>修改字段名和数据类型<br>ALTER TABLE 表名 CHANGE 旧字段名  新字段名 新数据类型;<br> 例子：<code>alter table student change sbirthday sbirth date;</code></li>
</ul>
</li>
<li><h4 id="字段约束添加"><a href="#字段约束添加" class="headerlink" title="字段约束添加"></a>字段约束添加</h4><ul>
<li>外键约束添加删除<ul>
<li>添加：ALTER TABLE 从表<br>ADD CONSTRAINT 外键名 foreign key (被加字段) references 主表(主表字段);</li>
<li>删除:alter table table_name drop foreign key 约束名</li>
</ul>
</li>
<li>主键添加删除<ul>
<li>先删后加:</li>
<li>删除:alter table table_name drop primary key</li>
<li>添加:alter table table_name add primary key (字段)</li>
</ul>
</li>
<li>复合主键添加<ul>
<li>多个字段组合而成的主键，表级约束</li>
<li>PRIMARY KEY (字段名1,字段名2,…)</li>
</ul>
</li>
<li>非空约束添加删除<ul>
<li>添加:alter table table_name modify 列名 数据类型 not null </li>
<li>删除:alter table table_name modify 列名 数据类型 null</li>
</ul>
</li>
<li>唯一约束添加删除<ul>
<li>添加:alter table table_name add unique 约束名（字段） </li>
<li>删除:alter table table_name drop key 约束名</li>
</ul>
</li>
<li>自增添加删除<ul>
<li>添加:alter table table_name modify 列名 int auto_increment </li>
<li>删除:alter table table_name modify 列名 int</li>
</ul>
</li>
<li>默认值添加删除<ul>
<li>添加:alter table table_name alter 列名 set default ‘值’ </li>
<li>删除:alter table table_name alter 列名 drop default</li>
</ul>
</li>
</ul>
</li>
<li><p>查看表字段信息</p>
<ul>
<li>查看表的字段信息：desc table_name;</li>
</ul>
</li>
</ul>
<h1 id="4-对数据表数据操作"><a href="#4-对数据表数据操作" class="headerlink" title="4.对数据表数据操作"></a>4.对数据表数据操作</h1><ul>
<li><h4 id="添加数据"><a href="#添加数据" class="headerlink" title="添加数据"></a>添加数据</h4><ul>
<li><p>为所有字段添加数据<br>INSERT [INTO] 数据表名(字段列表)  VALUES(值列表);<br><code>insert into student(sno,sname,ssex) values(&#39;1001&#39;,&#39;曹操&#39;,&#39;男&#39;);</code></p>
<p>如果向表中所有字段插入数据，可以省略字段列，可以写成：<br><code>insert into student values(&#39;1002&#39;,&#39;刘备&#39;,&#39;男&#39;);</code></p>
</li>
<li><p>为部分字段添加数据</p>
<p>​	INSERT [INTO] 数据表名 (字段名1 [, 字段名2] …) VALUES (值1[, 值2] …);<br>​	INSERT [INTO] 数据表名 SET 字段名1 &#x3D; 值1 [, 字段名2 &#x3D; 值2]<br>​	字段名必须与数据相对应 一次只能加一条<br>​	<code>insert into student set sno=&#39;1004&#39;,sname=&#39;貂蝉&#39;,classno=&#39;19004&#39;;</code><br>​	<code>insert into student(sno,sname,classno) values (&#39;1003&#39;,&#39;吕布&#39;,&#39;19001&#39;);</code></p>
</li>
<li><p>一次添加多行数据<br>INSERT [INTO] 数据表名 [(字段列表)] VALUES (值列表) [, (值列表)] …;</p>
</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">  insert into student values</span><br><span class="line">  (&#x27;1005&#x27;,&#x27;司马懿&#x27;,null,null,null,null,&#x27;19003&#x27;),</span><br><span class="line">(&#x27;1006&#x27;,&#x27;大乔&#x27;,&#x27;女&#x27;,null,null,null,&#x27;19003&#x27;),</span><br><span class="line">  (&#x27;1007&#x27;,&#x27;小乔&#x27;,&#x27;女&#x27;,null,null,null,&#x27;19003&#x27;);</span><br></pre></td></tr></table></figure>
</li>
<li><h4 id="删除数据"><a href="#删除数据" class="headerlink" title="删除数据"></a>删除数据</h4><p>DELETE FROM 数据表名 [WHERE 条件表达式];<br>有WHERE条件，删除满足条件的记录，无WHERE条件，系统就会自动删除该表中所有的记录。<br>删除example01表中所有记录：<code>select * from example01;</code><br>删除student表sno字段为1002的记录：<code>delete from student where sno = &#39;1002&#39;;</code></p>
</li>
<li><h4 id="查询数据"><a href="#查询数据" class="headerlink" title="查询数据"></a>查询数据</h4><p>查询当前表数据： <code>SELECT * FROM 数据表名</code></p>
</li>
<li><h4 id="修改数据"><a href="#修改数据" class="headerlink" title="修改数据"></a>修改数据</h4><p>UPDATE 数据表名 SET 字段名1 &#x3D; 值1 [, 字段名2 &#x3D; 值2, …][WHERE 条件表达式];<br>有WHERE条件，修改符合要求的对应字段，无WHERE条件，修改表中所有对应的字段。<br><code>update student set classno = &#39;19003&#39; where sname = &#39;貂蝉&#39;;</code></p>
</li>
</ul>
<h2 id="5-数据导出"><a href="#5-数据导出" class="headerlink" title="5.数据导出"></a>5.数据导出</h2><ul>
<li><h4 id="使用-SELECT-…-INTO-OUTFILE-语句导出数据"><a href="#使用-SELECT-…-INTO-OUTFILE-语句导出数据" class="headerlink" title="使用 SELECT … INTO OUTFILE 语句导出数据"></a>使用 SELECT … INTO OUTFILE 语句导出数据</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">SELECT 导出的字段 FROM 导出的表 </span><br><span class="line">INTO OUTFILE &#x27;路径(双反斜杠)&#x27; </span><br><span class="line">character set utf8</span><br><span class="line">FIELDS</span><br><span class="line">	TERMINATED BY &#x27;,&#x27;		-- 字段之间用,间隔   默认&#x27;\t&#x27;</span><br><span class="line">	ENCLOSED BY &#x27;\&quot;&#x27;		-- 设置字段包围字符</span><br><span class="line">	ESCAPED BY &#x27;\&#x27;&#x27;			-- 转义字符，默认为&#x27;\&#x27;</span><br><span class="line">LINES</span><br><span class="line">	STARTING BY &#x27;val&#x27;;		-- 每行的开头字符，默认不使用任何字符</span><br><span class="line">	TERMINATED BY &#x27;\r\n&#x27;;	-- 每行的结尾字符，默认为&#x27;\n&#x27;</span><br><span class="line"></span><br><span class="line">-- 例子：</span><br><span class="line">SELECT * FROM student </span><br><span class="line">INTO OUTFILE &#x27;C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\myschool35stu.txt&#x27; </span><br><span class="line">character set utf8</span><br><span class="line">FIELDS</span><br><span class="line">	TERMINATED BY &#x27;,&#x27;</span><br><span class="line">	ENCLOSED BY &#x27;\&quot;&#x27;</span><br><span class="line">	ESCAPED BY &#x27;\&#x27;&#x27;</span><br><span class="line">LINES</span><br><span class="line">	TERMINATED BY &#x27;\r\n&#x27;;</span><br></pre></td></tr></table></figure></li>
</ul>
<h2 id="6-视图"><a href="#6-视图" class="headerlink" title="6.视图"></a>6.视图</h2><ul>
<li>创建视图命令：<code> create view 视图名字 as</code></li>
<li>删除视图命令：<code>drop view 视图名;</code></li>
<li>修改视图数据：<code>update view_s set sname = &#39;安琪拉&#39; where sno = &#39;2005020301&#39;</code></li>
<li>删除视图数据：<code>delete from view_s where sname =&#39;安琪拉&#39;</code></li>
</ul>
<h2 id="7-索引"><a href="#7-索引" class="headerlink" title="7.索引"></a>7.索引</h2><ul>
<li><p>创建索引</p>
<ul>
<li><p>建表时</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE &lt;表名&gt;</span><br><span class="line">	(</span><br><span class="line">    &lt;字段1&gt; &lt;数据类型1&gt; [&lt;列级完整性约束条件1&gt;],</span><br><span class="line">    &lt;字段2&gt; &lt;数据类型2&gt; [&lt;列级完整性约束条件2&gt;],</span><br><span class="line">    [UNIQUE|FULLTEXT|SPATIAL] &lt;INDEX|KEY&gt; [索引名](属性名[(长度)] [,…]) </span><br><span class="line">	);</span><br><span class="line"></span><br><span class="line">create table test_index</span><br><span class="line">	(</span><br><span class="line">	tid int,</span><br><span class="line">	tname varchar(20),</span><br><span class="line">	tcount double,</span><br><span class="line">	index index_tid (tid) -- 普通索引</span><br><span class="line">    unique index index_test(tid,tname) -- 唯一索引</span><br><span class="line">	);</span><br></pre></td></tr></table></figure>
</li>
<li><p>建表后</p>
<ul>
<li>create语句：<code>create [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 on 表(字段);</code></li>
<li>alter语句：<code>ALTER TABLE 表名 ADD  [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 (属性名[(长度)] [,…]);</code></li>
</ul>
</li>
</ul>
</li>
</ul>
<h2 id="6-用户管理"><a href="#6-用户管理" class="headerlink" title="6.用户管理"></a>6.用户管理</h2><h3 id="1-查看用户"><a href="#1-查看用户" class="headerlink" title="1.查看用户"></a>1.查看用户</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select user,host from mysql.user;</span><br></pre></td></tr></table></figure>

<h3 id="2-创建用户"><a href="#2-创建用户" class="headerlink" title="2.创建用户"></a>2.创建用户</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">不带密码</span><br><span class="line">create user user1@localhost;</span><br><span class="line">带密码</span><br><span class="line">create user userA@localhost identified by &#x27;123&#x27;</span><br><span class="line">创建多个</span><br><span class="line">create user</span><br><span class="line">user2@localhost identified by &#x27;user2&#x27;,</span><br><span class="line">user3@localhost identified by &#x27;user3&#x27;;</span><br></pre></td></tr></table></figure>

<h3 id="3-删除用户"><a href="#3-删除用户" class="headerlink" title="3.删除用户"></a>3.删除用户</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">drop user test1,tets2@localhost;</span><br></pre></td></tr></table></figure>

<h3 id="4-修改密码"><a href="#4-修改密码" class="headerlink" title="4.修改密码"></a>4.修改密码</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ALTER USER 账户名 IDENTIFIED BY &#x27;明文密码&#x27;;</span><br></pre></td></tr></table></figure>

<h3 id="5-修改用户名"><a href="#5-修改用户名" class="headerlink" title="5.修改用户名"></a>5.修改用户名</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">rename user 旧用户名 to 新用户名;</span><br><span class="line">将用户user1和user2的名字分别修改为test1和test2。</span><br><span class="line">rename user &#x27;user1&#x27; to &#x27;test1&#x27;,&#x27;user2&#x27;@&#x27;localhost&#x27; to &#x27;tets2&#x27;@&#x27;localhost&#x27;;</span><br></pre></td></tr></table></figure>

<h2 id="7-权限管理"><a href="#7-权限管理" class="headerlink" title="7.权限管理"></a>7.权限管理</h2><h3 id="1-查看权限"><a href="#1-查看权限" class="headerlink" title="1.查看权限"></a>1.查看权限</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">看自己</span><br><span class="line">show grants</span><br><span class="line">看别人</span><br><span class="line">show grants for root@localhost</span><br></pre></td></tr></table></figure>

<h3 id="2-授予权限"><a href="#2-授予权限" class="headerlink" title="2.授予权限"></a>2.授予权限</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">授予test1用户对所有数据有查询和插入权限</span><br><span class="line">create user test1@localhost identified by &#x27;123&#x27;;</span><br><span class="line">grant select,insert</span><br><span class="line">on *.*</span><br><span class="line">to test1@localhost;</span><br><span class="line"></span><br><span class="line">gradem数据库中student表的DELETE权限授予用户user4。</span><br><span class="line">grant delete</span><br><span class="line">on gradem.student</span><br><span class="line">to user4@localhost;</span><br><span class="line"></span><br><span class="line">添加多个和grant权限</span><br><span class="line">grant delete,select,create,drop</span><br><span class="line">on gradem.student</span><br><span class="line">to user4@localhost</span><br><span class="line">with grant OPTION;</span><br><span class="line"></span><br><span class="line">使用GRANT语句将gradem数据库中sc表的degree列和cno列的UPDATE权限授予用户test1。</span><br><span class="line">grant update(degree,cno)</span><br><span class="line">on gradem.sc</span><br><span class="line">to test1@localhost;</span><br></pre></td></tr></table></figure>

<h3 id="3-回收权限"><a href="#3-回收权限" class="headerlink" title="3.回收权限"></a>3.回收权限</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">REVOKE语句收回test8用户的的所有权限，包括GRANT权限。</span><br><span class="line">revoke all privileges,grant option</span><br><span class="line">from test8@localhost;</span><br><span class="line"></span><br><span class="line">收回用户指定的权限 收回test1用户对gradem数据库中sc表的cno列的UPDATE权限</span><br><span class="line">revoke update(cno) </span><br><span class="line">on gradem.sc</span><br><span class="line">from test1@localhost;</span><br></pre></td></tr></table></figure>

<h2 id="7-存储函数"><a href="#7-存储函数" class="headerlink" title="7.存储函数"></a>7.存储函数</h2><p>存储函数格式：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">CREATE FUNCTION 函数名([参数名 数据类型, …]) RETURNS 返回值类型</span><br><span class="line">			[BEGIN]</span><br><span class="line">			 # 函数体</span><br><span class="line">			 RETURN 返回值数据; # 数据必须与结构中定义的返回值类型一致</span><br><span class="line">			[END]</span><br></pre></td></tr></table></figure>

<p>不带参数函数：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">delimiter $$</span><br><span class="line">	create function sayhello() returns varchar(20)</span><br><span class="line">	begin</span><br><span class="line">		return &#x27;hello world！&#x27;;</span><br><span class="line">	end</span><br><span class="line">	$$</span><br><span class="line">	delimiter ;</span><br></pre></td></tr></table></figure>

<p>带参数函数</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">-- 带参数的函数。创建一个名为func_name的存储函数，返回某位老师的姓名。</span><br><span class="line">delimiter $$</span><br><span class="line">	create function func_name(t_tno varchar(10)) returns varchar(20)</span><br><span class="line">	begin</span><br><span class="line">		return (select tname from teacher where tno = t_tno);</span><br><span class="line">	end</span><br><span class="line">	$$ </span><br><span class="line">	delimiter ;</span><br></pre></td></tr></table></figure>

<p>查看函数创建语句</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SHOW CREATE FUNCTION sayhello \G</span><br></pre></td></tr></table></figure>

<p>查看函数状态语句</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SHOW FUNCTION STATUS LIKE &#x27;sayHello&#x27; \G</span><br></pre></td></tr></table></figure>

<p>调用函数</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SELECT 函数名1(实参列表), 函数名2(实参列表), …;</span><br><span class="line">select sayhello();</span><br></pre></td></tr></table></figure>

<p>删除函数</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">DROP FUNCTION [IF EXISTS] 函数名;</span><br><span class="line">drop function sayhello;</span><br></pre></td></tr></table></figure>

<h4 id="一些系统函数："><a href="#一些系统函数：" class="headerlink" title="一些系统函数："></a>一些系统函数：</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">-- 将字符串转换为全部大写。</span><br><span class="line">SELECT UPPER(&#x27;Welcome to My SQL&#x27;);</span><br><span class="line">-- 将字符串转换为全部小写。</span><br><span class="line">SELECT LOWER(&#x27;Welcome to My SQL&#x27;);</span><br><span class="line">-- 去掉字符串前后的空格。</span><br><span class="line">SELECT TRIM(&#x27;  Welcome to My SQL  &#x27;);</span><br><span class="line">-- 截取从第12个字符开始的10个字符。</span><br><span class="line">SELECT MID(&#x27;Welcome to My SQL&#x27;,12,10);</span><br><span class="line"></span><br><span class="line">#使用日期型函数，获得输出结果见下表。</span><br><span class="line">#输出结果：</span><br><span class="line">#年份 月份 日期 星期几</span><br><span class="line">#2009 11 18 2</span><br><span class="line">SELECT YEAR(&#x27;2009-11-18&#x27;)  年份,MONTH(&#x27;2009-11-18&#x27;) 月份,DAY(&#x27;2009-11-18&#x27;) 日期,</span><br><span class="line">WEEKDAY(&#x27;2009-11-18&#x27;)  星期几;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="8-存储过程"><a href="#8-存储过程" class="headerlink" title="8.存储过程"></a>8.存储过程</h2><p>存储过程格式</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">DELIMITER 新结束符号</span><br><span class="line">				CREATE PROCEDURE 过程名字([[ IN | OUT | INOUT ] 参数名称 参数类型])</span><br><span class="line">				BEGIN</span><br><span class="line">				  过程体</span><br><span class="line">				END</span><br><span class="line">				新结束符号</span><br><span class="line">				DELIMITER ;</span><br></pre></td></tr></table></figure>

<p>无参过程</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">-- 从数据库gradem的student表中检索出所有籍贯为“青岛”的学生的学号、姓名、班级号及家庭地址等信息。</span><br><span class="line">delimiter $$</span><br><span class="line">	create procedure proc_stud()</span><br><span class="line">	begin</span><br><span class="line">		select sno,sname,classno,saddress </span><br><span class="line">		from student </span><br><span class="line">		where saddress like &#x27;%青岛%&#x27;;		</span><br><span class="line">	end</span><br><span class="line">	$$</span><br><span class="line">	delimiter ;</span><br></pre></td></tr></table></figure>

<p>有参过程</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">delimiter $$</span><br><span class="line">	create procedure proc_sc(in tmp_sno varchar(10))</span><br><span class="line">	begin</span><br><span class="line">		select * from sc </span><br><span class="line">		where sno = tmp_sno;</span><br><span class="line">	end</span><br><span class="line">	$$</span><br><span class="line">	delimiter ;</span><br></pre></td></tr></table></figure>

<p>有参有返回值过程</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">-- 创建一个名为proc_sc_num的存储过程，统计某位同学的考试门数。</span><br><span class="line">delimiter $$</span><br><span class="line">	create procedure proc_sc_num(in tmp_sno varchar(10),out count_num int)</span><br><span class="line">	begin</span><br><span class="line">		select count(*) into count_num</span><br><span class="line">		from sc </span><br><span class="line">		where sno = tmp_sno;</span><br><span class="line">	end</span><br><span class="line">	$$</span><br><span class="line">	delimiter ;</span><br></pre></td></tr></table></figure>

<p>查看存储过程创建语句</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SHOW CREATE PROCEDURE 过程名;</span><br><span class="line">show create procedure proc_sc_num;</span><br></pre></td></tr></table></figure>

<p>查看存储过程状态信息</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SHOW PROCEDURE STATUS [LIKE 匹配模式];</span><br><span class="line">SHOW PROCEDURE STATUS LIKE &#x27;proc%&#x27;\G</span><br></pre></td></tr></table></figure>

<p>调用存储过程</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">CALL 数据库名.存储过程名称([实参列表]);</span><br><span class="line"></span><br><span class="line">-- 无参</span><br><span class="line">call proc_stud();</span><br><span class="line">-- 有参</span><br><span class="line">call proc_sc(&#x27;2007010104&#x27;);</span><br><span class="line">-- 有参有返回值</span><br><span class="line">call proc_sc_num(&#x27;2007010104&#x27;,@num);</span><br><span class="line">select @num;</span><br></pre></td></tr></table></figure>

<h2 id="9-变量"><a href="#9-变量" class="headerlink" title="9.变量"></a>9.变量</h2><h3 id="系统变量"><a href="#系统变量" class="headerlink" title="系统变量"></a>系统变量</h3><ul>
<li><p>概念：系统变量也可称为全局变量，指的就是MySQL系统内部定义的变量，对所有MySQL客户端都有效。默认情况下，会在服务器启动时使用命令行上的选项或配置文件（ini文件）完成系统变量的设置。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SHOW [GLOBAL | SESSION] VARIABLES  [LIKE &#x27;匹配模式&#x27;]</span><br><span class="line">-- 查看当前mysql版本</span><br><span class="line">SELECT @@version </span><br></pre></td></tr></table></figure>
</li>
<li><p>修改系统变量</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">-- 永久生效</span><br><span class="line"># 语法1</span><br><span class="line">SET GLOBAL 变量名 = 值;</span><br><span class="line"># 语法2</span><br><span class="line">SET @@GLOBAL.变量名 = 值;</span><br><span class="line"></span><br><span class="line">-- 仅本次链接生效</span><br><span class="line">SET 变量名 = 新值;</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="用户变量"><a href="#用户变量" class="headerlink" title="用户变量"></a>用户变量</h3><ul>
<li><p>概念：基于会话变量实现的, 可以暂存值, 并传递给同一连接里的下一条sql使用的变量。当客户端连接退出时,变量会被释放。</p>
</li>
<li><p>用户变量的定义：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"># 语法1</span><br><span class="line">SET @变量名 =表达式;</span><br><span class="line"># 语法2</span><br><span class="line">SELECT @变量名 :=表达式;</span><br><span class="line"></span><br><span class="line">-- 查询结果赋值给变量</span><br><span class="line">SET @student=(SELECT sname FROM student </span><br><span class="line">              WHERE sno=&#x27;2007010120&#x27;);</span><br><span class="line"></span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="局部变量"><a href="#局部变量" class="headerlink" title="局部变量"></a>局部变量</h3><ul>
<li><p>概念：局部变量的作用范围仅在复合语句语法BEGIN和END语句之间，保证局部变量在除BEGIN和END之间以外的任何地方，不能被获取和修改。</p>
</li>
<li><p>定义局部变量格式：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">DECLARE 变量名1 [, 变量名2] … 数据类型 [DEFAULT 默认值]</span><br><span class="line">-- 省略default时变量的初始值为NULL</span><br></pre></td></tr></table></figure>
</li>
<li><p>例子</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">-- 创建一个名为addr的局部变量，并在select语句中使用该局部变量查找位于青岛的所有学生的学号和名字。</span><br><span class="line">DELIMITER $$</span><br><span class="line">CREATE PROCEDURE proc_setaddr() </span><br><span class="line">	begin</span><br><span class="line">		DECLARE addr varchar(50);</span><br><span class="line">		set addr = &#x27;青岛&#x27;;</span><br><span class="line">		select sno,sname </span><br><span class="line">		from student</span><br><span class="line">		where saddress like concat(&#x27;%&#x27;,addr,&#x27;%&#x27;);</span><br><span class="line">	end</span><br><span class="line">	$$</span><br><span class="line">DELIMITER ;</span><br></pre></td></tr></table></figure>
</li>
<li><p>使用SELECT…into为变量赋值</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">-- 查找‘计算机工程系’的专业主任，将主任名字保存在变量中。</span><br><span class="line">DELIMITER $$</span><br><span class="line">CREATE PROCEDURE proc_into_dept(in dept_value varchar(50)) </span><br><span class="line">	begin</span><br><span class="line">		DECLARE deptheader_value varchar(50);</span><br><span class="line">		select deptheader into deptheader_value</span><br><span class="line">		from department</span><br><span class="line">		where deptname = dept_value;</span><br><span class="line">		select dept_value 专业, deptheader_value 专业主任;</span><br><span class="line">	end</span><br><span class="line">	$$</span><br><span class="line">DELIMITER ;</span><br></pre></td></tr></table></figure></li>
</ul>
<h2 id="10-流程控制语句"><a href="#10-流程控制语句" class="headerlink" title="10.流程控制语句"></a>10.流程控制语句</h2><h3 id="条件结构"><a href="#条件结构" class="headerlink" title="条件结构"></a>条件结构</h3><ul>
<li><p>if…else 用于指定SQL语句的执行条件</p>
<ul>
<li><p>格式</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">IF search_condition THEN statement_list</span><br><span class="line">[ELSEIF search_condition THEN statement_list]…</span><br><span class="line">[ELSE statement_list]</span><br><span class="line">END IF;</span><br></pre></td></tr></table></figure>
</li>
<li><p>例子</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line">-- 查询‘2007010104’同学‘a01’ 课程的成绩，如果成绩大于90分，则显示“考的不错！”，否则显示“加油！”。</span><br><span class="line">DELIMITER $$</span><br><span class="line">CREATE PROCEDURE proc_if() </span><br><span class="line">	begin</span><br><span class="line">	  DECLARE d_value int;</span><br><span class="line">	  select degree into d_value </span><br><span class="line">	  from sc </span><br><span class="line">	  where sno = &#x27;2007010104&#x27; and cno = &#x27;a01&#x27;;</span><br><span class="line">	  if d_value &gt;90 then </span><br><span class="line">	      select concat(&#x27;考了&#x27;,d_value,&#x27;,考的不错！&#x27;) as 成绩 ;</span><br><span class="line">	  else  </span><br><span class="line">	     select concat(&#x27;考了&#x27;,d_value,&#x27;,加油！&#x27;) as 成绩  ;</span><br><span class="line">	  end if;</span><br><span class="line">	end</span><br><span class="line">	$$</span><br><span class="line">DELIMITER ;</span><br><span class="line"></span><br><span class="line">-- select 输出内容 as 字段名    可以在结果中新建一列并输出</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p>case语句</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">DELIMITER $$</span><br><span class="line">CREATE PROCEDURE proc_level(IN score DECIMAL(5, 2))</span><br><span class="line">BEGIN</span><br><span class="line">	CASE</span><br><span class="line">	    WHEN score &gt; 89 THEN SELECT &#x27;优秀&#x27; as 等级;</span><br><span class="line">	    WHEN score &gt; 79 THEN SELECT &#x27;良好&#x27; as 等级;</span><br><span class="line">	    WHEN score &gt; 69 THEN SELECT &#x27;中等&#x27; as 等级;</span><br><span class="line">	    WHEN score &gt; 59 THEN SELECT &#x27;及格&#x27; as 等级;</span><br><span class="line">             ELSE SELECT &#x27;不及格&#x27; as 等级;</span><br><span class="line">    	 END CASE;</span><br><span class="line">END</span><br><span class="line">$$</span><br><span class="line">DELIMITER ;</span><br><span class="line"></span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="循环结构"><a href="#循环结构" class="headerlink" title="循环结构"></a>循环结构</h3><ul>
<li><p>while…end循环</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">-- 使用while语句求1 ～ 100的和</span><br><span class="line">DELIMITER $$</span><br><span class="line">CREATE PROCEDURE proc_while() </span><br><span class="line">begin</span><br><span class="line">	DECLARE i,sum int DEFAULT 0;</span><br><span class="line">	WHILE i&lt;=100 DO</span><br><span class="line">	  SET sum=sum+i;</span><br><span class="line">     	  SET i=i+1;</span><br><span class="line">	END WHILE;</span><br><span class="line">	SELECT sum as 求和;</span><br><span class="line">end</span><br><span class="line">$$</span><br><span class="line">DELIMITER ;</span><br></pre></td></tr></table></figure>
</li>
<li><p>repeat…end循环</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">-- 使用REPEAT语句求1 ～ 100的奇数和</span><br><span class="line">DELIMITER $$</span><br><span class="line">CREATE PROCEDURE proc_repeat() </span><br><span class="line">begin</span><br><span class="line">  DECLARE i,sum int DEFAULT 0;</span><br><span class="line">  repeat</span><br><span class="line">      if i%2 != 0 then</span><br><span class="line">        set sum = sum+i;</span><br><span class="line">      end if;</span><br><span class="line">      SET i=i+1;</span><br><span class="line">  until i&gt;100</span><br><span class="line"> end repeat;</span><br><span class="line">  SELECT i,sum ;</span><br><span class="line">end</span><br><span class="line">$$</span><br><span class="line">DELIMITER ;</span><br></pre></td></tr></table></figure>
</li>
<li><p>loop…end循环</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">-- 使用LOOP语句求1 ～ 100的和</span><br><span class="line">DELIMITER $$</span><br><span class="line">CREATE PROCEDURE proc_loop() </span><br><span class="line">begin</span><br><span class="line">  DECLARE i,sum int DEFAULT 0;</span><br><span class="line">  add_sum:LOOP</span><br><span class="line"> 	if i&gt;100 then </span><br><span class="line">	  LEAVE add_sum;</span><br><span class="line">	else</span><br><span class="line">	  set sum = sum+i;</span><br><span class="line">	  set i = i+1;</span><br><span class="line">	end if;</span><br><span class="line">  end loop;</span><br><span class="line">  SELECT i,sum ;</span><br><span class="line">end</span><br><span class="line">$$</span><br><span class="line">DELIMITER ;</span><br></pre></td></tr></table></figure>
</li>
<li><p>例子</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">-- 利用循环往表中插入大量数据</span><br><span class="line"></span><br><span class="line">CREATE TABLE `gradem`.`test_auto_insert`  </span><br><span class="line">(</span><br><span class="line">  `tid` int(11) NOT NULL,</span><br><span class="line">  `name` varchar(20) ,</span><br><span class="line">  `number` varchar(20) </span><br><span class="line">) ENGINE = InnoDB CHARACTER SET = utf8;</span><br><span class="line"></span><br><span class="line">DELIMITER $$</span><br><span class="line">CREATE PROCEDURE proc_million() </span><br><span class="line">begin</span><br><span class="line">	DECLARE i bigint DEFAULT 1;</span><br><span class="line">	DECLARE num bigint DEFAULT 101;</span><br><span class="line">	WHILE i&lt;=1000001 DO</span><br><span class="line">	  insert into test_auto_insert(tid,name,number) values(i,concat(&#x27;测试&#x27;,i),num);</span><br><span class="line">    SET i=i+1;</span><br><span class="line">	END WHILE;</span><br><span class="line">end</span><br><span class="line">$$</span><br><span class="line">DELIMITER ;</span><br></pre></td></tr></table></figure></li>
</ul>
<h2 id="11-触发器"><a href="#11-触发器" class="headerlink" title="11.触发器"></a>11.触发器</h2><h3 id="0-格式"><a href="#0-格式" class="headerlink" title="0.格式"></a>0.格式</h3><figure class="highlight txt"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">要素：触发器的要素：触发事件类型，触发时间，触发对象。</span><br><span class="line">		事件类型：增删改，三种类型，insert、delete和update；</span><br><span class="line">		触发时间：操作前还是操作后：before和after；</span><br><span class="line">		触发对象：表中的每一条记录（行）</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">创建触发器</span><br><span class="line">	语法格式：CREATE TRIGGER 触发器名 触发时机 触发事件 </span><br><span class="line">				ON 表名 </span><br><span class="line">				FOR EACH ROW  触发顺序</span><br><span class="line">				BEGIN</span><br><span class="line">				  操作的内容</span><br><span class="line">				END</span><br></pre></td></tr></table></figure>

<h3 id="1-插入触发器"><a href="#1-插入触发器" class="headerlink" title="1.插入触发器"></a>1.插入触发器</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">delimiter $$</span><br><span class="line">create  trigger trig_ssex_student before insert</span><br><span class="line">on student</span><br><span class="line">for each row </span><br><span class="line">begin</span><br><span class="line">	if(new.ssex!=&#x27;男&#x27; and new.ssex!=&#x27;女&#x27;)then</span><br><span class="line">		set new.ssex=&#x27;男&#x27;;</span><br><span class="line">	end if;</span><br><span class="line">end</span><br><span class="line">$$</span><br><span class="line">delimiter ;</span><br></pre></td></tr></table></figure>

<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">#1.创建插入触发器trig_insert。当向销售订单表（Sell_Order）添加一条销售订单信息时，同时需要更新商品表（Product）中的“现有库存量（ProductStockNumber）”列和“已经销售的商品量（ProductSellNumber）”列。</span><br><span class="line">delimiter $$</span><br><span class="line">create  trigger trig_insert after insert</span><br><span class="line">on sellorder</span><br><span class="line">for each row </span><br><span class="line">begin</span><br><span class="line">	update product set ProductStockNumber=ProductStockNumber-new.SellOrderNumber where ProductID=new.ProductID;</span><br><span class="line">	update product set ProductSellNumber=ProductSellNumber+new.SellOrderNumber where ProductID=new.ProductID;</span><br><span class="line">end</span><br><span class="line">$$</span><br><span class="line">delimiter ;</span><br></pre></td></tr></table></figure>

<h3 id="2-删除触发器"><a href="#2-删除触发器" class="headerlink" title="2.删除触发器"></a>2.删除触发器</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">delimiter $$</span><br><span class="line">create  trigger student_delete before delete</span><br><span class="line">on student</span><br><span class="line">for each row </span><br><span class="line">begin</span><br><span class="line">		SET @@foreign_key_checks=OFF;</span><br><span class="line">		delete from sc where old.sno=sno;</span><br><span class="line">end</span><br><span class="line">$$</span><br><span class="line">delimiter ;</span><br></pre></td></tr></table></figure>

<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">#2.创建删除触发器trig_delete。当向销售订单表（Sell_Order）删除一条销售订单信息时，同时需要更新商品表（Product）中的“现有库存量（ProductStockNumber）”列和“已经销售的商品量（ProductSellNumber）”列。</span><br><span class="line">delimiter $$</span><br><span class="line">create  trigger trig_delete after delete</span><br><span class="line">on sellorder</span><br><span class="line">for each row </span><br><span class="line">begin</span><br><span class="line">	update product set ProductStockNumber=ProductStockNumber+old.SellOrderNumber where ProductID=old.ProductID;</span><br><span class="line">	update product set ProductSellNumber=ProductSellNumber-old.SellOrderNumber where ProductID=old.ProductID;</span><br><span class="line">end</span><br><span class="line">$$</span><br><span class="line">delimiter ;</span><br></pre></td></tr></table></figure>

<h3 id="3-更新触发器"><a href="#3-更新触发器" class="headerlink" title="3.更新触发器"></a>3.更新触发器</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">delimiter $$</span><br><span class="line">create  trigger student_sno before update</span><br><span class="line">on student</span><br><span class="line">for each row </span><br><span class="line">begin</span><br><span class="line">		update sc set sno = new.sno where sno = old.sno;</span><br><span class="line">end</span><br><span class="line">$$</span><br><span class="line">delimiter ;</span><br></pre></td></tr></table></figure>

<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">#3.创建更新触发器trig_update。当供应商表（Provider）中的“供应商编号（ProviderID）”变更时，同时更新表中相应的采购订单表（Purchase_order）的“供应商编号（ProviderID）”信息。</span><br><span class="line">delimiter $$</span><br><span class="line">create  trigger trig_update after update</span><br><span class="line">on provider</span><br><span class="line">for each row </span><br><span class="line">begin</span><br><span class="line">	update purchase_order set ProviderID=new.ProviderID	where ProviderID=old.ProviderID;</span><br><span class="line">end</span><br><span class="line">$$</span><br><span class="line">delimiter ;</span><br></pre></td></tr></table></figure>

<h3 id="4-查看触发器-show-triggers"><a href="#4-查看触发器-show-triggers" class="headerlink" title="4.查看触发器 show triggers"></a>4.查看触发器<code> show triggers</code></h3><h3 id="5-删除触发器DROP-TRIGGER-触发器名"><a href="#5-删除触发器DROP-TRIGGER-触发器名" class="headerlink" title="5.删除触发器DROP TRIGGER 触发器名"></a>5.删除触发器<code>DROP TRIGGER 触发器名</code></h3></article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="http://example.com">学途路漫漫</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="http://example.com/2020/04/03/Mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/">http://example.com/2020/04/03/Mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="http://example.com" target="_blank">学途路漫漫</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/MySql/">MySql</a></div><div class="post_share"><div class="social-share" data-image="https://s1.ax1x.com/2023/04/02/ppfOMMd.jpg" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/butterfly-extsrc/sharejs/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/butterfly-extsrc/sharejs/dist/js/social-share.min.js" defer></script></div></div><div class="post-reward"><div class="reward-button"><i class="fas fa-qrcode"></i> 打赏</div><div class="reward-main"><ul class="reward-all"><li class="reward-item"><a href="/img/wechat.png" target="_blank"><img class="post-qr-code-img" src="/img/wechat.png" alt="wechat"/></a><div class="post-qr-code-desc">wechat</div></li><li class="reward-item"><a href="/img/alipay.png" target="_blank"><img class="post-qr-code-img" src="/img/alipay.png" alt="alipay"/></a><div class="post-qr-code-desc">alipay</div></li></ul></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-full"><a href="/2020/05/07/JavaScript%E5%85%A5%E9%97%A8%E7%AC%94%E8%AE%B0/" title="JavaScript入门"><img class="cover" src="https://s1.ax1x.com/2023/04/03/pph4Wo4.jpg" onerror="onerror=null;src='/img/404.jpg'" alt="cover of previous post"><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">JavaScript入门</div></div></a></div></nav></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="is-center"><div class="avatar-img"><img src="/img/avatar.gif" onerror="this.onerror=null;this.src='/img/friend_404.gif'" alt="avatar"/></div><div class="author-info__name">学途路漫漫</div><div class="author-info__description">今日事，今日毕</div></div><div class="card-info-data site-data is-center"><a href="/archives/"><div class="headline">文章</div><div class="length-num">7</div></a><a href="/tags/"><div class="headline">标签</div><div class="length-num">8</div></a><a href="/categories/"><div class="headline">分类</div><div class="length-num">10</div></a></div><div class="card-info-social-icons is-center"><a class="social-icon" href="https://gitee.com/code-jay" target="_blank" title="Gitee"><i class="fab fa-github"></i></a><a class="social-icon" href="mailto:1940879828@qq.com" target="_blank" title="Email"><i class="fas fa-envelope"></i></a><a class="social-icon" href="https://music.163.com/#/user/home?id=306898885" target="_blank" title="Music"><i class="fa-solid fa-music"></i></a></div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span><span class="toc-percentage"></span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#1-%E6%95%B0%E6%8D%AE%E5%BA%93%E6%93%8D%E4%BD%9C"><span class="toc-number">1.</span> <span class="toc-text">1.数据库操作</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#2-%E6%95%B0%E6%8D%AE%E8%A1%A8%E6%93%8D%E4%BD%9C"><span class="toc-number">2.</span> <span class="toc-text">2.数据表操作</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BF%AE%E6%94%B9%E8%A1%A8%E5%90%8D"><span class="toc-number">2.0.0.1.</span> <span class="toc-text">修改表名</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BF%AE%E6%94%B9%E8%A1%A8%E7%9A%84%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E"><span class="toc-number">2.0.0.2.</span> <span class="toc-text">修改表的存储引擎</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BF%AE%E6%94%B9%E8%A1%A8%E7%9A%84%E5%AD%97%E7%AC%A6%E9%9B%86"><span class="toc-number">2.0.0.3.</span> <span class="toc-text">修改表的字符集</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%A4%8D%E5%88%B6%E8%A1%A8"><span class="toc-number">2.0.0.4.</span> <span class="toc-text">复制表</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%88%A0%E9%99%A4%E8%A1%A8"><span class="toc-number">2.0.0.5.</span> <span class="toc-text">删除表</span></a></li></ol></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#3-%E5%AD%97%E6%AE%B5%E6%93%8D%E4%BD%9C"><span class="toc-number">3.</span> <span class="toc-text">3.字段操作</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%A2%9E%E5%88%A0%E6%94%B9%E5%AD%97%E6%AE%B5%E6%93%8D%E4%BD%9C"><span class="toc-number">3.0.0.1.</span> <span class="toc-text">增删改字段操作</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%AD%97%E6%AE%B5%E7%BA%A6%E6%9D%9F%E6%B7%BB%E5%8A%A0"><span class="toc-number">3.0.0.2.</span> <span class="toc-text">字段约束添加</span></a></li></ol></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#4-%E5%AF%B9%E6%95%B0%E6%8D%AE%E8%A1%A8%E6%95%B0%E6%8D%AE%E6%93%8D%E4%BD%9C"><span class="toc-number">4.</span> <span class="toc-text">4.对数据表数据操作</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E6%B7%BB%E5%8A%A0%E6%95%B0%E6%8D%AE"><span class="toc-number">4.0.0.1.</span> <span class="toc-text">添加数据</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%88%A0%E9%99%A4%E6%95%B0%E6%8D%AE"><span class="toc-number">4.0.0.2.</span> <span class="toc-text">删除数据</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E6%9F%A5%E8%AF%A2%E6%95%B0%E6%8D%AE"><span class="toc-number">4.0.0.3.</span> <span class="toc-text">查询数据</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BF%AE%E6%94%B9%E6%95%B0%E6%8D%AE"><span class="toc-number">4.0.0.4.</span> <span class="toc-text">修改数据</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-%E6%95%B0%E6%8D%AE%E5%AF%BC%E5%87%BA"><span class="toc-number">4.1.</span> <span class="toc-text">5.数据导出</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BD%BF%E7%94%A8-SELECT-%E2%80%A6-INTO-OUTFILE-%E8%AF%AD%E5%8F%A5%E5%AF%BC%E5%87%BA%E6%95%B0%E6%8D%AE"><span class="toc-number">4.1.0.1.</span> <span class="toc-text">使用 SELECT … INTO OUTFILE 语句导出数据</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#6-%E8%A7%86%E5%9B%BE"><span class="toc-number">4.2.</span> <span class="toc-text">6.视图</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#7-%E7%B4%A2%E5%BC%95"><span class="toc-number">4.3.</span> <span class="toc-text">7.索引</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#6-%E7%94%A8%E6%88%B7%E7%AE%A1%E7%90%86"><span class="toc-number">4.4.</span> <span class="toc-text">6.用户管理</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E6%9F%A5%E7%9C%8B%E7%94%A8%E6%88%B7"><span class="toc-number">4.4.1.</span> <span class="toc-text">1.查看用户</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E5%88%9B%E5%BB%BA%E7%94%A8%E6%88%B7"><span class="toc-number">4.4.2.</span> <span class="toc-text">2.创建用户</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-%E5%88%A0%E9%99%A4%E7%94%A8%E6%88%B7"><span class="toc-number">4.4.3.</span> <span class="toc-text">3.删除用户</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-%E4%BF%AE%E6%94%B9%E5%AF%86%E7%A0%81"><span class="toc-number">4.4.4.</span> <span class="toc-text">4.修改密码</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-%E4%BF%AE%E6%94%B9%E7%94%A8%E6%88%B7%E5%90%8D"><span class="toc-number">4.4.5.</span> <span class="toc-text">5.修改用户名</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#7-%E6%9D%83%E9%99%90%E7%AE%A1%E7%90%86"><span class="toc-number">4.5.</span> <span class="toc-text">7.权限管理</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E6%9F%A5%E7%9C%8B%E6%9D%83%E9%99%90"><span class="toc-number">4.5.1.</span> <span class="toc-text">1.查看权限</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E6%8E%88%E4%BA%88%E6%9D%83%E9%99%90"><span class="toc-number">4.5.2.</span> <span class="toc-text">2.授予权限</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-%E5%9B%9E%E6%94%B6%E6%9D%83%E9%99%90"><span class="toc-number">4.5.3.</span> <span class="toc-text">3.回收权限</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#7-%E5%AD%98%E5%82%A8%E5%87%BD%E6%95%B0"><span class="toc-number">4.6.</span> <span class="toc-text">7.存储函数</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%B8%80%E4%BA%9B%E7%B3%BB%E7%BB%9F%E5%87%BD%E6%95%B0%EF%BC%9A"><span class="toc-number">4.6.0.1.</span> <span class="toc-text">一些系统函数：</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#8-%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B"><span class="toc-number">4.7.</span> <span class="toc-text">8.存储过程</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#9-%E5%8F%98%E9%87%8F"><span class="toc-number">4.8.</span> <span class="toc-text">9.变量</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%B3%BB%E7%BB%9F%E5%8F%98%E9%87%8F"><span class="toc-number">4.8.1.</span> <span class="toc-text">系统变量</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%94%A8%E6%88%B7%E5%8F%98%E9%87%8F"><span class="toc-number">4.8.2.</span> <span class="toc-text">用户变量</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%B1%80%E9%83%A8%E5%8F%98%E9%87%8F"><span class="toc-number">4.8.3.</span> <span class="toc-text">局部变量</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#10-%E6%B5%81%E7%A8%8B%E6%8E%A7%E5%88%B6%E8%AF%AD%E5%8F%A5"><span class="toc-number">4.9.</span> <span class="toc-text">10.流程控制语句</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9D%A1%E4%BB%B6%E7%BB%93%E6%9E%84"><span class="toc-number">4.9.1.</span> <span class="toc-text">条件结构</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%BE%AA%E7%8E%AF%E7%BB%93%E6%9E%84"><span class="toc-number">4.9.2.</span> <span class="toc-text">循环结构</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#11-%E8%A7%A6%E5%8F%91%E5%99%A8"><span class="toc-number">4.10.</span> <span class="toc-text">11.触发器</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#0-%E6%A0%BC%E5%BC%8F"><span class="toc-number">4.10.1.</span> <span class="toc-text">0.格式</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E6%8F%92%E5%85%A5%E8%A7%A6%E5%8F%91%E5%99%A8"><span class="toc-number">4.10.2.</span> <span class="toc-text">1.插入触发器</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E5%88%A0%E9%99%A4%E8%A7%A6%E5%8F%91%E5%99%A8"><span class="toc-number">4.10.3.</span> <span class="toc-text">2.删除触发器</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-%E6%9B%B4%E6%96%B0%E8%A7%A6%E5%8F%91%E5%99%A8"><span class="toc-number">4.10.4.</span> <span class="toc-text">3.更新触发器</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-%E6%9F%A5%E7%9C%8B%E8%A7%A6%E5%8F%91%E5%99%A8-show-triggers"><span class="toc-number">4.10.5.</span> <span class="toc-text">4.查看触发器 show triggers</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-%E5%88%A0%E9%99%A4%E8%A7%A6%E5%8F%91%E5%99%A8DROP-TRIGGER-%E8%A7%A6%E5%8F%91%E5%99%A8%E5%90%8D"><span class="toc-number">4.10.6.</span> <span class="toc-text">5.删除触发器DROP TRIGGER 触发器名</span></a></li></ol></li></ol></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item"><a class="thumbnail" href="/2023/03/10/vue3%E5%BF%AB%E9%80%9F%E4%B8%8A%E6%89%8B/" title="vue3快速上手"><img src="https://user-images.githubusercontent.com/499550/93624428-53932780-f9ae-11ea-8d16-af949e16a09f.png" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="vue3快速上手"/></a><div class="content"><a class="title" href="/2023/03/10/vue3%E5%BF%AB%E9%80%9F%E4%B8%8A%E6%89%8B/" title="vue3快速上手">vue3快速上手</a><time datetime="2023-03-10T06:20:40.000Z" title="发表于 2023-03-10 14:20:40">2023-03-10</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2020/12/01/laravel%E6%A1%86%E6%9E%B6%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/" title="laravel框架学习笔记"><img src="https://s1.ax1x.com/2023/04/03/pph4doQ.jpg" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="laravel框架学习笔记"/></a><div class="content"><a class="title" href="/2020/12/01/laravel%E6%A1%86%E6%9E%B6%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/" title="laravel框架学习笔记">laravel框架学习笔记</a><time datetime="2020-12-01T12:06:29.000Z" title="发表于 2020-12-01 20:06:29">2020-12-01</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2020/06/25/git%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/" title="git常用命令"><img src="https://s1.ax1x.com/2023/04/03/pph46yV.jpg" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="git常用命令"/></a><div class="content"><a class="title" href="/2020/06/25/git%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/" title="git常用命令">git常用命令</a><time datetime="2020-06-25T08:00:29.000Z" title="发表于 2020-06-25 16:00:29">2020-06-25</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2020/06/22/Cookie%E6%9C%AC%E5%9C%B0%E5%AD%98%E5%82%A8/" title="Cookie本地存储"><img src="https://s1.ax1x.com/2023/04/03/pph4Ysf.png" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="Cookie本地存储"/></a><div class="content"><a class="title" href="/2020/06/22/Cookie%E6%9C%AC%E5%9C%B0%E5%AD%98%E5%82%A8/" title="Cookie本地存储">Cookie本地存储</a><time datetime="2020-06-22T07:47:29.000Z" title="发表于 2020-06-22 15:47:29">2020-06-22</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2020/06/05/php%E5%85%A5%E9%97%A8%E7%AC%94%E8%AE%B0/" title="php入门笔记"><img src="https://s1.ax1x.com/2023/04/03/pph4qeO.jpg" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="php入门笔记"/></a><div class="content"><a class="title" href="/2020/06/05/php%E5%85%A5%E9%97%A8%E7%AC%94%E8%AE%B0/" title="php入门笔记">php入门笔记</a><time datetime="2020-06-05T08:27:40.000Z" title="发表于 2020-06-05 16:27:40">2020-06-05</time></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2020 - 2023 By 学途路漫漫</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="translateLink" type="button" title="简繁转换">简</button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="回到顶部"><span class="scroll-percent"></span><i class="fas fa-arrow-up"></i></button></div></div><div><script src="/js/utils.js"></script><script src="/js/main.js"></script><script src="/js/tw_cn.js"></script><script src="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox.umd.min.js"></script><div class="js-pjax"></div><script defer="defer" id="ribbon" src="https://cdn.jsdelivr.net/npm/butterfly-extsrc/dist/canvas-ribbon.min.js" size="150" alpha="0.6" zIndex="-1" mobile="false" data-click="true"></script><script src="https://cdn.jsdelivr.net/npm/butterfly-extsrc/dist/activate-power-mode.min.js"></script><script>POWERMODE.colorful = true;
POWERMODE.shake = false;
POWERMODE.mobile = false;
document.body.addEventListener('input', POWERMODE);
</script><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script></div></body></html>